package com.lanvideo.dao;

import com.lanvideo.entity.LearningNote;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 学习笔记数据访问层
 */
@Repository
public class LearningNoteDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存笔记
     */
    public int saveNote(LearningNote note) {
        String sql = "INSERT INTO learning_note (user_id, video_id, title, content, position) VALUES (?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql, note.getUserId(), note.getVideoId(), 
                note.getTitle(), note.getContent(), note.getPosition());
    }

    /**
     * 更新笔记
     */
    public int updateNote(LearningNote note) {
        String sql = "UPDATE learning_note SET title = ?, content = ?, position = ?, updated_time = CURRENT_TIMESTAMP WHERE id = ?";
        return jdbcTemplate.update(sql, note.getTitle(), note.getContent(), note.getPosition(), note.getId());
    }

    /**
     * 删除笔记
     */
    public int deleteNote(Integer id) {
        String sql = "DELETE FROM learning_note WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID获取笔记
     */
    public LearningNote getNoteById(Integer id) {
        String sql = "SELECT ln.*, v.title as video_title, u.name as user_name " +
                "FROM learning_note ln " +
                "LEFT JOIN video v ON ln.video_id = v.id " +
                "LEFT JOIN user u ON ln.user_id = u.id " +
                "WHERE ln.id = ?";
        List<LearningNote> notes = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningNote.class), id);
        return notes.isEmpty() ? null : notes.get(0);
    }

    /**
     * 获取用户的所有笔记
     */
    public List<LearningNote> getUserNotes(Integer userId) {
        String sql = "SELECT ln.*, v.title as video_title, u.name as user_name " +
                "FROM learning_note ln " +
                "LEFT JOIN video v ON ln.video_id = v.id " +
                "LEFT JOIN user u ON ln.user_id = u.id " +
                "WHERE ln.user_id = ? " +
                "ORDER BY ln.created_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningNote.class), userId);
    }

    /**
     * 获取视频的所有笔记
     */
    public List<LearningNote> getVideoNotes(Integer videoId) {
        String sql = "SELECT ln.*, v.title as video_title, u.name as user_name " +
                "FROM learning_note ln " +
                "LEFT JOIN video v ON ln.video_id = v.id " +
                "LEFT JOIN user u ON ln.user_id = u.id " +
                "WHERE ln.video_id = ? " +
                "ORDER BY ln.position ASC, ln.created_time ASC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningNote.class), videoId);
    }

    /**
     * 获取用户对特定视频的笔记
     */
    public List<LearningNote> getUserVideoNotes(Integer userId, Integer videoId) {
        String sql = "SELECT ln.*, v.title as video_title, u.name as user_name " +
                "FROM learning_note ln " +
                "LEFT JOIN video v ON ln.video_id = v.id " +
                "LEFT JOIN user u ON ln.user_id = u.id " +
                "WHERE ln.user_id = ? AND ln.video_id = ? " +
                "ORDER BY ln.position ASC, ln.created_time ASC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningNote.class), userId, videoId);
    }

    /**
     * 搜索笔记
     */
    public List<LearningNote> searchNotes(Integer userId, String keyword) {
        String sql = "SELECT ln.*, v.title as video_title, u.name as user_name " +
                "FROM learning_note ln " +
                "LEFT JOIN video v ON ln.video_id = v.id " +
                "LEFT JOIN user u ON ln.user_id = u.id " +
                "WHERE ln.user_id = ? AND (ln.title LIKE ? OR ln.content LIKE ?) " +
                "ORDER BY ln.created_time DESC";
        String searchPattern = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LearningNote.class), userId, searchPattern, searchPattern);
    }

    /**
     * 获取用户的笔记数量
     */
    public int getUserNoteCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM learning_note WHERE user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId);
        return count != null ? count : 0;
    }

    /**
     * 获取视频的笔记数量
     */
    public int getVideoNoteCount(Integer videoId) {
        String sql = "SELECT COUNT(*) FROM learning_note WHERE video_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, videoId);
        return count != null ? count : 0;
    }
}
